<?php
class class_Balances
{
	private $_db; 
	function __construct()
	{
		$this->config();
	}
	
	function method_getOS ($params, $error)
	{
		$this->_db->setQuery("SELECT
		cod_obra_social as value, 
		descripcion as label
		FROM obras_sociales
		ORDER BY descripcion
		");
		$this->_db->alter();
    	if (mysql_error()) { $error->SetError(JsonRpcError_Unknown, (__FILE__ . " - " . (__LINE__ - 2) . ": " . mysql_error())); return $error; }
    	return $this->_db->loadResult();
	}
	
	function method_getPlanes ($params, $error)
	{
		$this->_db->setQuery("SELECT
		cod_plan as value, 
		descripcion as label
		FROM planes
		WHERE cod_obra_social = '" . $params[0]->cod_obra_social . "'
		ORDER BY descripcion
		");
		$this->_db->alter();
    	if (mysql_error()) { $error->SetError(JsonRpcError_Unknown, (__FILE__ . " - " . (__LINE__ - 2) . ": " . mysql_error())); return $error; }
    	return $this->_db->loadResult();
	}
	
	function method_getPlanillas ($params, $error)
    {
    	$this->_db->setQuery("SELECT
    	planillas.cod_planilla_cobro,
    	obras_sociales.tabla,
    	obras_sociales.descripcion as os,
    	planes.descripcion as plan,
    	CONCAT(planillas.nro_factura, ' - ', planillas.tipo_factura) as factura,
    	CONCAT(DATE_FORMAT(planillas.fecha, '%d/%m/%Y'), ' - ', planillas.hora) as fechayhora
    	FROM planillas
    	INNER JOIN obras_sociales USING(cod_obra_social)
    	LEFT JOIN planes USING(cod_plan)
    	WHERE estado = 'C' 
    	");
    	$this->_db->alter();
    	if (mysql_error()) { $error->SetError(JsonRpcError_Unknown, (__FILE__ . " - " . (__LINE__ - 2) . ": " . mysql_error())); return $error; }
    	$regs = $this->_db->loadResult();
    	$rows = array();
    	FOREACH ($regs as $reg)
    	{
    		$this->_db->setQuery("SELECT 
    		SUM(cobrado) as cobrado 
    		FROM os_" . $reg->tabla . " 
    		WHERE cod_planilla_cobro = '" . $reg->cod_planilla_cobro . "'
    		");
    		$this->_db->alter();
	    	if (mysql_error()) { $error->SetError(JsonRpcError_Unknown, (__FILE__ . " - " . (__LINE__ - 2) . ": " . mysql_error())); return $error; }
	    	$cobrado = $this->_db->loadResult();
	    	$cobrado = $cobrado[0]->cobrado;
	    	
	    	$this->_db->setQuery("SELECT 
    		SUM(monto) as ingresos 
    		FROM planillas_balances
    		WHERE operacion = 'I' 
    		AND cod_planilla_cobro = '" . $reg->cod_planilla_cobro . "'
    		");
    		$this->_db->alter();
	    	if (mysql_error()) { $error->SetError(JsonRpcError_Unknown, (__FILE__ . " - " . (__LINE__ - 2) . ": " . mysql_error())); return $error; }
	    	$ingresos = $this->_db->loadResult();
	    	$ingresos = $ingresos[0]->ingresos;
	    	
	    	$this->_db->setQuery("SELECT 
    		SUM(monto) as egresos
    		FROM planillas_balances
    		WHERE operacion = 'E' 
    		AND cod_planilla_cobro = '" . $reg->cod_planilla_cobro . "'
    		");
    		$this->_db->alter();
	    	if (mysql_error()) { $error->SetError(JsonRpcError_Unknown, (__FILE__ . " - " . (__LINE__ - 2) . ": " . mysql_error())); return $error; }
	    	$egresos = $this->_db->loadResult();
	    	$egresos = $egresos[0]->egresos;
	    	
    		$row = array();
    		array_push($row, $reg->cod_planilla_cobro);
    		array_push($row, $reg->os);
    		array_push($row, $reg->plan);
    		array_push($row, $reg->factura);
    		array_push($row, $reg->fechayhora);
    		array_push($row, $cobrado);
    		array_push($row, $ingresos);
    		array_push($row, $egresos);
    		array_push($rows, $row);
    	}
		return $rows;
//        $this->_db->setQuery("
//        	");
//        $this->_db->alter();
//    	if (mysql_error()) { $error->SetError(JsonRpcError_Unknown, (__FILE__ . " - " . (__LINE__ - 2) . ": " . mysql_error())); return $error; }
//    	$this->_db->loadResult();
    }
    function method_getBalances ($params, $error)
    {
    	$this->_db->setQuery("SELECT
    	cod_planilla_balance,
    	(IF(operacion='I','Ingreso','Egreso')) as operacion,
    	monto,
    	fecha_op,
    	(CASE metodo_op WHEN 'C' THEN 'Cheque' WHEN 'D' THEN 'Deposito' WHEN 'E' THEN 'Efectivo' END) as metodo_op,
    	destino,
    	fecha,
    	estado
    	FROM planillas_balances
    	WHERE cod_planilla_cobro = '" . $params[0]->cod_planilla_cobro . "'
    	");
    	$this->_db->alter();
    	if (mysql_error()) { $error->SetError(JsonRpcError_Unknown, (__FILE__ . " - " . (__LINE__ - 2) . ": " . mysql_error())); return $error; }
    	$regs = $this->_db->loadResult();
    	$rows = array();
    	FOREACH ($regs as $reg)
    	{
    		$row = array();
    		array_push($row, $reg->cod_planilla_balance);
    		array_push($row, $reg->operacion);
    		array_push($row, $reg->monto);
    		array_push($row, $reg->fecha_op);
    		array_push($row, $reg->metodo_op);
    		array_push($row, $reg->destino);
    		array_push($row, $reg->fecha);
    		array_push($row, $reg->estado);
    		array_push($rows, $row);
    	}
		return $rows;
    }
    function method_getBalanceSele ($params, $error)
    {
    	$this->_db->setQuery("SELECT 
    	operacion,
    	monto,
    	DATE_FORMAT(fecha_op, '%d/%m/%Y') as fecha_op,
    	DATE_FORMAT(fecha_corres, '%d/%m/%Y') as fecha_corres,
    	metodo_op,
    	observaciones_op,
    	destino,
    	observaciones_dest,
    	estado
    	FROM planillas_balances 
    	WHERE cod_planilla_balance = '" . $params[0]->cod_planilla_balance . "'");
    	$this->_db->alter();
    	if (mysql_error()) { $error->SetError(JsonRpcError_Unknown, (__FILE__ . " - " . (__LINE__ - 2) . ": " . mysql_error())); return $error; }
    	$balance = $this->_db->loadResult();
    	return $balance[0];
    }
    function method_guardarBalance ($params, $error)
    {
    	if ($params[0]->op == 'alta')
    	{
	    	$this->_db->setQuery("
	    	INSERT INTO planillas_balances
	    	(cod_planilla_cobro,
	    	operacion,
	    	monto,
	    	fecha_op,
	    	fecha_corres,
	    	metodo_op,
	    	observaciones_op,
	    	destino,
	    	observaciones_dest,
	    	fecha,
	    	hora
	    	)
	    	VALUES
	    	('" . $params[0]->cod_planilla_cobro . "',
	    	'" . $params[0]->operacion . "',
	    	'" . $params[0]->monto . "',
	    	'" . $params[0]->fecha_op . "',
	    	'" . $params[0]->fecha_corres . "',
	    	'" . $params[0]->metodo_op . "',
	    	'" . $params[0]->observaciones_op . "',
	    	'" . $params[0]->destino . "',
	    	'" . $params[0]->observaciones_dest . "',
	    	'" . date('Y-m-d') . "',
	    	'" . date('H:i:s') . "')
	    	");
    	}
    	else
    	{
    		$this->_db->setQuery("UPDATE planillas_balances SET
    		operacion = '" . $params[0]->operacion . "',
    		monto = '" . $params[0]->monto . "',
    		fecha_op = '" . $params[0]->fecha_op . "',
    		fecha_corres = '" . $params[0]->fecha_corres . "',
    		metodo_op = '" . $params[0]->metodo_op . "',
    		observaciones_op = '" . $params[0]->observaciones_op . "',
    		destino = '" . $params[0]->destino . "',
    		observaciones_dest = '" . $params[0]->observaciones_dest . "',
    		fecha = '" . date('Y-m-d') . "',
    		hora = '" . date('H:i:s') . "'
    		WHERE cod_planilla_balance = '" . $params[0]->cod_planilla_balance . "' 
    		LIMIT 1");
    	}
    	$this->_db->alter();
	    if (mysql_error()) { $error->SetError(JsonRpcError_Unknown, (__FILE__ . " - " . (__LINE__ - 2) . ": " . mysql_error())); return $error; }
	    return true;
    }
    
    function method_borrarBalance ($params, $error)
    {
    	$this->_db->setQuery("
    	DELETE FROM planillas_balances
    	WHERE cod_planilla_balance = '" . $params[0]->cod_planilla_balance . "'
    	LIMIT 1
    	");
    	$this->_db->alter();
    	if (mysql_error()) { $error->SetError(JsonRpcError_Unknown, (__FILE__ . " - " . (__LINE__ - 2) . ": " . mysql_error())); return $error; }
    	return true;
    }
    
	function method_marcarRendidos ($params, $error)
    {
    	$Rendidos = "";
    	FOREACH ($params[0]->selection as $sel)
    	{
    		$Rendidos .= "'" . $sel . "', ";
    	}
    	$Rendidos .= "''";
    	
    	$this->_db->setQuery("
    	UPDATE planillas_balances SET estado = 'R'
    	WHERE cod_planilla_balance IN (" . $Rendidos . ")
    	");
    	$this->_db->alter();
    	if (mysql_error()) { $error->SetError(JsonRpcError_Unknown, (__FILE__ . " - " . (__LINE__ - 2) . ": " . mysql_error())); return $error; }
    	return true;
    }
    
    function method_listarBalances ($params, $error)
    {
    	$F_OS = "";
    	$F_PLAN = "";
    	$F_OP = "";
		$F_MOP = "";
		$F_FECHA = "";
		
    	IF ($params[0]->os != "")
    	{
    		$F_OS = " AND planillas.cod_obra_social = '" . $params[0]->os . "' ";
    	}
    	IF ($params[0]->plan != "")
    	{
    		$F_PLAN = " AND planillas.cod_plan = '" . $params[0]->plan . "' ";
    	}
    	IF ($params[0]->op != "")
    	{
    		$F_OP = " AND planillas_balances.operacion = '" . $params[0]->op . "' ";
    	}
    	IF ($params[0]->metodo_op != "")
    	{
    		$F_MOP = " AND planillas_balances.metodo_op = '" . $params[0]->metodo_op . "' ";
    	}
    	IF ($params[0]->desde != "")
    	{
    		IF ($params[0]->hasta != "")
    		{
    			IF ($params[0]->fecha == 'O')
    			{
    				// Fecha Operacion
    				$F_FECHA = " AND fecha_op BETWEEN '" . $params[0]->desde . "' AND '" . $params[0]->hasta . "' ";
    			}
    			ELSE
    			{
    				// Fecha Correspondiente a
    				$F_FECHA = " AND fecha_corres BETWEEN '" . $params[0]->desde . "' AND '" . $params[0]->hasta . "' ";
    			}
    		}
    		ELSE
    		{
    			IF ($params[0]->fecha == 'O')
    			{
    				// Fecha Operacion
    				$F_FECHA = " AND fecha_op >= '" . $params[0]->desde . "' ";
    			}
    			ELSE
    			{
    				// Fecha Correspondiente a
    				$F_FECHA = " AND fecha_corres >= '" . $params[0]->desde . "' ";
    			}
    		}
    	}
    	ELSE
    	{
    		IF ($params[0]->hasta != "")
    		{
    			IF ($params[0]->fecha == 'O')
    			{
    				// Fecha Operacion
    				$F_FECHA = " AND fecha_op <= '" . $params[0]->hasta . "' ";
    			}
    			ELSE
    			{
    				// Fecha Correspondiente a
    				$F_FECHA = " AND fecha_corres <= '" . $params[0]->hasta . "' ";
    			}
    		}
    	}
    	
    	$F_RENDIDAS = " AND planillas_balances.estado = '" . $params[0]->rendidos . "' ";
    	
		$this->_db->setQuery("SELECT 
		cod_planilla_balance,
		operacion,
		monto,
		fecha_op,
		fecha_corres,
		(CASE metodo_op WHEN 'C' THEN 'Cheque' WHEN 'D' THEN 'Deposito' WHEN 'E' THEN 'Efectivo' END) as metodo_op,
		observaciones_op,
		destino,
		observaciones_dest,
		CONCAT(obras_sociales.descripcion, ' - ', (IFNULL(planes.descripcion, ''))) as os_plan
		FROM planillas_balances
		INNER JOIN planillas ON planillas_balances.cod_planilla_cobro = planillas.cod_planilla_cobro " . $F_OS . " " . $F_PLAN . " 
		INNER JOIN obras_sociales ON planillas.cod_obra_social = obras_sociales.cod_obra_social 
		LEFT JOIN planes ON planillas.cod_plan = planes.cod_plan 
		WHERE 1=1
		" . $F_OP . "
		" . $F_MOP . "
		" . $F_FECHA . "
		" . $F_RENDIDAS . "
		");
    	$this->_db->alter();
    	if (mysql_error()) { $error->SetError(JsonRpcError_Unknown, (__FILE__ . " - " . (__LINE__ - 2) . ": " . mysql_error())); return $error; }
    	$regs = $this->_db->loadResult();
    	$rows = array();
    	$TOTAL_I = 0;
    	$TOTAL_E = 0;
    	FOREACH ($regs as $reg)
    	{
    		IF ($reg->operacion == 'I')
    		{
    			$TOTAL_I += $reg->monto;
    		}
    		ELSE
    		{
    			$TOTAL_E += $reg->monto;
    		}
    		$row = array();
    		array_push($row, $reg->cod_planilla_balance);
    		array_push($row, $reg->operacion);
    		array_push($row, $reg->monto);
    		array_push($row, $reg->fecha_op);
    		array_push($row, $reg->fecha_corres);
    		array_push($row, $reg->metodo_op);
    		array_push($row, $reg->observaciones_op);
    		array_push($row, $reg->destino);
    		array_push($row, $reg->observaciones_dest);
    		array_push($row, $reg->os_plan);
    		array_push($rows, $row);
    	}
    	$result = "";
    	$result->grilla = $rows;
    	$result->total_i = number_format($TOTAL_I, 2, ",", ".");
    	$result->total_e = number_format($TOTAL_E, 2, ",", ".");
    	return $result;
    }
	private function config(){
		require_once("conexion.php");
		require_once("DataBase.php");
		$this->_db = new DataBase($SERVIDOR,$USUARIO,$PASSWORD,$BASE);
		mysql_query("SET NAMES utf8");
	}
}
?>